package main

import (
	"fmt"
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
)


func main(){
	fmt.Println("hello mysql")
	/*
	user@unix(/path/to/socket)/dbname?charset=utf8
	user:password@tcp(localhost:5555)/dbname?charset=utf8
	user:password@/dbname
	user:password@tcp([de:ad:be:ef::ca:fe]:80)/dbname
	*/
	db,err:=sql.Open("mysql","root:root@/zxp?charset=utf8")//"user:password@tcp(ip:port)/database"
	checkErr(err)
	
	//insert
	stmt,err:=db.Prepare("insert userinfo set username=?,departname=?,created=?")
	checkErr(err)
	
	res,err:=stmt.Exec("Jack","RD","2014-12-9")
	checkErr(err)
	
	id,err:=res.LastInsertId()
	checkErr(err)
	
	fmt.Println("inserted row ID: ",id)
	
	//update
	stmt,err=db.Prepare("update userinfo set username=? where uid=?")
	checkErr(err)
	res,err=stmt.Exec("Jackie",id)
	checkErr(err)	
	affect,err:=res.RowsAffected()
	checkErr(err)
	fmt.Println("update affected: ",affect)
	
	//query
	rows,err:=db.Query("select * from userinfo")
	checkErr(err)
	
	for rows.Next(){
		var uid int
		var username string
		var departname string
		var created string
		err=rows.Scan(&uid,&username,&departname,&created)
		checkErr(err)
		fmt.Println("uid=",uid)
		fmt.Println("username=",username)
		fmt.Println("departname=",departname)
		fmt.Println("created=",created)
	}
	
	//delete
	stmt,err=db.Prepare("delete from userinfo where uid=?")
	checkErr(err)
	
	res,err=stmt.Exec(id)
	checkErr(err)
	
	affect,err=res.RowsAffected()
	checkErr(err)
	fmt.Println("delete affect: ",affect)
	
	db.Close()
	
}

func checkErr(err error){
	if err!=nil {
		panic(err)
	}
}
/*
CREATE TABLE `userinfo` (
    `uid` INT(10) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(64) NULL DEFAULT NULL,
    `departname` VARCHAR(64) NULL DEFAULT NULL,
    `created` DATE NULL DEFAULT NULL,
    PRIMARY KEY (`uid`)
)

CREATE TABLE `userdetail` (
    `uid` INT(10) NOT NULL DEFAULT '0',
    `intro` TEXT NULL,
    `profile` TEXT NULL,
    PRIMARY KEY (`uid`)
)
*/
